<report title="OMD RSV Consider High Risk Patient Search" description="Reports patients with high risk less than 2 years of age to consider for Beyfortus due to Diagnostic Code in Registry or Billing. Modified by OMD 2026-10-15" active="1"> 
   <query> 
 SELECT 

    CONCAT("<a target=new href=../../demographic/demographiccontrol.jsp?demographic_no=", de.demographic_no, "&displaymode=edit&dboperation=search_detail>", de.demographic_no, "<a/>") AS "Dem#",

    de.last_name AS "Last Name",
    de.first_name AS "First Name",
    CONCAT(de.year_of_birth, '-', de.month_of_birth, '-', de.date_of_birth) AS DOB,
    TIMESTAMPDIFF(YEAR, CONCAT(de.year_of_birth, '-', de.month_of_birth, '-', de.date_of_birth), CURDATE()) AS Age,
de.phone AS "Phone",
    (select value FROM demographicExt WHERE demographic_no=de.demographic_no AND key_val="demo_cell" limit 1) as "Cell",
    de.email AS "Email",


    dr.dxresearch_code AS "Dx Code",

    bi.dx AS "Billing Code 1",
    bi.dx1 AS "Billing Code 2",
    bi.dx2 AS "Billing Code 3",

    de.provider_no AS "Provider",

    CONCAT( "<a  target='_blank' href=../../oscarEncounter/IncomingEncounter.do?appointmentNo=&demographicNo=",de.demographic_no,"&curProviderNo=&reason=Tel-Progress+Note&encType=&curDate=",CURDATE(),"&appointmentDate=&startTime=&status=>eChart<a/>" ) AS "eChart" 


FROM 
    demographic de

LEFT JOIN dxresearch dr ON de.demographic_no = dr.demographic_no 
    AND (
        dr.dxresearch_code LIKE '277%' 
        OR dr.dxresearch_code LIKE '279%' 
        OR dr.dxresearch_code LIKE '358%' 
        OR dr.dxresearch_code LIKE '359%' 
        OR dr.dxresearch_code LIKE '429%' 
        OR dr.dxresearch_code LIKE '518%' 
        OR dr.dxresearch_code LIKE '746%'  
        OR dr.dxresearch_code LIKE '758%' 
        OR dr.dxresearch_code LIKE '2770%' 
        OR dr.dxresearch_code LIKE '3580%' 
        OR dr.dxresearch_code LIKE '7580%'
        OR dr.dxresearch_code LIKE '489%'
    )
    AND dr.status = 'A'

LEFT JOIN billing_on_cheader1 bc ON de.demographic_no = bc.demographic_no
LEFT JOIN billing_on_item bi ON bc.id = bi.ch1_id

WHERE 
    TIMESTAMPDIFF(MONTH, CONCAT(de.year_of_birth, '-', de.month_of_birth, '-', de.date_of_birth), CURDATE()) <= 24
    AND de.patient_status = 'AC'
    AND (
        dr.dxresearch_code IS NOT NULL
        OR
        (
            dr.dxresearch_code IS NULL 
            AND (
                bi.dx LIKE '277%' 
                OR bi.dx LIKE '279%' 
                OR bi.dx LIKE '358%' 
                OR bi.dx LIKE '359%' 
                OR bi.dx LIKE '429%' 
                OR bi.dx LIKE '518%' 
                OR bi.dx LIKE '746%'  
                OR bi.dx LIKE '758%' 
                OR bi.dx LIKE '2770%' 
                OR bi.dx LIKE '3580%' 
                OR bi.dx LIKE '7580%'
                OR bi.dx LIKE '489%'
            )
        )
    )
GROUP BY 
    de.demographic_no
ORDER BY 
    de.last_name;

</query>

</report>